"""
需求管理服务层
将需求相关的业务逻辑从API层迁移到服务层
"""
from datetime import datetime, timedelta
import logging
from typing import List, Optional, Dict, Any, Union
from bson import ObjectId, errors  # pyright: ignore[reportMissingImports]
from motor.motor_asyncio import AsyncIOMotorDatabase  # pyright: ignore[reportMissingImports]
from fastapi import HTTPException  # pyright: ignore[reportMissingImports]
try:
    from fastapi import status as http_status_module  # 避免与参数名 status 冲突
except ImportError:
    from starlette import status as http_status_module  # 后备导入
from app.models.requirement import (
    RequirementPriority,
)
# RequirementType 和 RequirementSource 已移除，改为使用统一配置管理
from app.schemas.requirement import (
    RequirementCreate, RequirementUpdate, RequirementResponse, RequirementComment,
    RequirementCommentCreate, RequirementCommentUpdate, RequirementStatusUpdate,
    RequirementBulkUpdate, RequirementStats, RequirementSummary, RequirementBoardColumn,
    RequirementAttachment
)
from app.services.user_service import UserService


class RequirementService:
    """需求服务类，封装需求相关的业务逻辑"""
    
    def __init__(self, db: AsyncIOMotorDatabase):
        self.db = db
        self._state_validator = None  # 缓存状态验证器实例
    
    @staticmethod
    def _get_status_code(status_name: str, default: int = 500) -> int:
        """安全地获取HTTP状态码"""
        try:
            if http_status_module is None:
                return default
            return getattr(http_status_module, status_name, default)
        except (AttributeError, TypeError):
            return default
    
    def _format_attachments(self, attachments: Any) -> List[RequirementAttachment]:
        """将数据库中的附件数据转换为响应模型，确保ID等字段为字符串"""
        if not attachments:
            return []

        formatted: List[RequirementAttachment] = []
        for raw in attachments:
            if not isinstance(raw, dict):
                continue

            attachment_id = raw.get("id") or raw.get("_id")
            uploaded_by = raw.get("uploaded_by")

            if not attachment_id or not uploaded_by:
                continue

            try:
                formatted.append(
                    RequirementAttachment(
                        id=str(attachment_id),
                        filename=raw.get("filename", ""),
                        original_filename=raw.get("original_filename") or raw.get("filename", ""),
                        file_size=int(raw.get("file_size") or 0),
                        file_type=raw.get("file_type", ""),
                        url=raw.get("url", ""),
                        uploaded_by=str(uploaded_by),
                        uploaded_at=raw.get("uploaded_at") or datetime.utcnow()
                    )
                )
            except Exception as exc:
                logging.getLogger(__name__).warning(
                    "格式化需求附件失败: %s (raw=%s)", exc, raw
                )
        return formatted
    
    def _to_object_id(self, value: Any, field_name: str = "ID") -> Optional[ObjectId]:
        """将值转换为ObjectId，如果无效则返回None"""
        if value is None:
            return None
        if isinstance(value, ObjectId):
            return value
        if isinstance(value, str) and ObjectId.is_valid(value):
            return ObjectId(value)
        return None
    
    def _validate_object_id(self, value: Any, field_name: str = "ID") -> ObjectId:
        """验证并转换ObjectId，如果无效则抛出异常"""
        obj_id = self._to_object_id(value, field_name)
        if obj_id is None:
            raise HTTPException(
                status_code=self._get_status_code('HTTP_400_BAD_REQUEST', 400),
                detail=f"无效的{field_name}格式"
            )
        return obj_id

    def _collect_id_options(self, value: Any) -> List[Any]:
        """生成用于查询的ID取值列表，兼容字符串和ObjectId"""
        options: List[Any] = []

        def _append_option(option: Any):
            if option is None:
                return
            key = f"{type(option).__name__}:{str(option)}"
            if key not in seen:
                seen.add(key)
                options.append(option)

        seen: set[str] = set()

        values = value if isinstance(value, (list, tuple, set)) else [value]
        for item in values:
            if item is None:
                continue
            obj_id = self._to_object_id(item)
            if obj_id:
                _append_option(obj_id)
            _append_option(item)

        return options

    async def _status_allows_unassigned(self, status: Optional[str]) -> bool:
        """
        判断在给定状态下是否允许缺少负责人
        从状态管理系统获取允许未分配的状态列表
        """
        if not status:
            return True
        try:
            from app.core.state_validator import get_state_validator
            state_validator = get_state_validator(self.db)
            # 获取允许未分配的状态（草稿、新建、待分配等）
            allowed_keywords = ["草稿", "新建", "待分配", "draft", "new", "open"]
            status_upper = status.upper() if status else ""
            
            # 检查状态是否匹配允许未分配的状态关键词
            from app.utils.state_utils import get_state_code_by_name_keywords
            for keyword in allowed_keywords:
                state_code = await get_state_code_by_name_keywords(
                    self.db,
                    "requirement", 
                    [keyword]
                )
                if state_code and state_code.upper() == status_upper:
                    return True
            
            # 如果无法从状态管理系统获取，使用关键词匹配作为后备
            status_lower = status.lower() if status else ""
            return status_lower in {"draft", "new", "open"}
        except Exception:
            # 异常情况下使用后备逻辑
            status_lower = status.lower() if status else ""
            return status_lower in {"draft", "new", "open"}

    async def _get_state_code_by_alias(self, module_name: str, aliases: List[str]) -> Optional[str]:
        """通过状态别名列表获取状态编码"""
        if not aliases:
            return None

        normalized_aliases = [alias.lower() for alias in aliases if alias]
        if not normalized_aliases:
            return None

        try:
            states = await self.get_module_states(module_name)
        except Exception:
            return None

        for state in states:
            code = getattr(state, "code", "") or ""
            name = getattr(state, "name", "") or ""
            code_lower = code.lower()
            name_lower = name.lower()
            for alias in normalized_aliases:
                if alias == code_lower or alias == name_lower or (alias and alias in name_lower):
                    return code
        return None
    
    async def _check_project_permission(self, user: dict, project_id: str, required_permission: str):
        """检查用户是否有项目权限"""
        from app.services.permission_service import PermissionService
        
        try:
            # 使用项目成员权限检查
            has_permission = await PermissionService.check_project_member_permission(
                project_id, user, self.db, required_permission
            )
            if not has_permission:
                permission_desc = PermissionService.get_permission_description(required_permission)
                raise HTTPException(
                    status_code=self._get_status_code('HTTP_403_FORBIDDEN', 403),
                    detail=f"权限不足: 需要 {permission_desc} ({required_permission}) 权限"
                )
        except HTTPException:
            raise
        except PermissionError as e:
            raise HTTPException(
                status_code=self._get_status_code('HTTP_403_FORBIDDEN', 403),
                detail=str(e)
            )
        except Exception as e:
            logger.error(f"Error checking project permission: {str(e)}")
            raise HTTPException(
                status_code=self._get_status_code('HTTP_403_FORBIDDEN', 403),
                detail=f"权限检查失败: {str(e)}"
            )
    
    async def _get_requirement_versions(self, requirement_id_obj: ObjectId) -> List[Dict[str, Any]]:
        """获取需求关联的版本列表"""
        try:
            # 查询版本需求关联表
            relations = await self.db.version_requirement_relations.find(
                {"requirement_id": requirement_id_obj}
            ).to_list(length=100)  # 限制最多100个版本关联
            
            if not relations:
                return []
            
            # 获取版本ID列表
            version_ids = [rel["version_id"] for rel in relations]
            
            # 查询版本信息
            versions = []
            async for version_doc in self.db.versions.find({"_id": {"$in": version_ids}}):
                versions.append({
                    "id": str(version_doc["_id"]),
                    "name": version_doc.get("name", ""),
                    "version_number": version_doc.get("version_number", ""),
                    "status": version_doc.get("status", ""),
                    "release_date": version_doc.get("planned_release_date") or version_doc.get("actual_release_date")
                })
            
            return versions
        except Exception as e:
            import logging
            logger = logging.getLogger(__name__)
            logger.warning(f"获取需求版本关联失败: {str(e)}")
            return []
    
    async def _batch_get_requirement_versions(self, requirement_ids: List[ObjectId]) -> Dict[str, List[Dict[str, Any]]]:
        """批量获取多个需求的版本信息"""
        try:
            if not requirement_ids:
                return {}
            
            # 初始化结果字典，确保所有需求ID都有对应的空列表
            result = {str(req_id): [] for req_id in requirement_ids}
            
            # 查询所有关联关系
            relations = await self.db.version_requirement_relations.find(
                {"requirement_id": {"$in": requirement_ids}}
            ).to_list(length=1000)  # 限制最多1000个关联关系
            
            if not relations:
                return result
            
            # 获取所有版本ID（去重）
            version_ids = list(set([rel["version_id"] for rel in relations if rel.get("version_id")]))
            
            if not version_ids:
                return result
            
            # 查询所有版本信息
            versions_dict = {}
            async for version_doc in self.db.versions.find({"_id": {"$in": version_ids}}):
                versions_dict[str(version_doc["_id"])] = {
                    "id": str(version_doc["_id"]),
                    "name": version_doc.get("name", ""),
                    "version_number": version_doc.get("version_number", ""),
                    "status": version_doc.get("status", ""),
                    "release_date": version_doc.get("planned_release_date") or version_doc.get("actual_release_date")
                }
            
            # 按需求ID分组版本信息
            for rel in relations:
                req_id_str = str(rel.get("requirement_id"))
                version_id_str = str(rel.get("version_id"))
                if req_id_str in result and version_id_str in versions_dict:
                    result[req_id_str].append(versions_dict[version_id_str])
            
            return result
        except Exception as e:
            import logging
            logger = logging.getLogger(__name__)
            logger.error(f"批量获取需求版本关联失败: {str(e)}", exc_info=True)
            # 确保异常时也返回所有需求ID的空列表
            return {str(req_id): [] for req_id in requirement_ids}
    
    async def _get_state_validator(self):
        """获取状态验证器（缓存实例）"""
        if self._state_validator is None:
            # 这里应该从状态服务获取，暂时返回一个简单实现
            class StateValidator:
                def __init__(self, db):
                    self.db = db
                
                async def get_state_info(self, module_name: str, state_code: str):
                    # 这里应该查询动态状态配置
                    # 暂时返回一个简化的实现
                    return {"code": state_code} if state_code else None
            
            self._state_validator = StateValidator(self.db)
        return self._state_validator
    
    @staticmethod
    def _get_user_display_name(user_doc: Optional[Dict[str, Any]]) -> Optional[str]:
        if not user_doc:
            return None
        return (
            user_doc.get("real_name")
            or user_doc.get("name")
            or user_doc.get("nickname")
            or user_doc.get("username")
            or user_doc.get("email")
        )
    
    def _build_query_filters(self,
                            user: dict,
                            project_id: Optional[str] = None,
                            status: Optional[List[str]] = None,
                            priority: Optional[List[str]] = None,
                            requirement_type: Optional[List[str]] = None,
                            source: Optional[List[str]] = None,
                            assignee_id: Optional[str] = None,
                            reporter_id: Optional[str] = None,
                            tags: Optional[List[str]] = None,
                            parent_id: Optional[str] = None,
                            created_at_start: Optional[datetime] = None,
                            created_at_end: Optional[datetime] = None) -> Dict[str, Any]:
        """构建查询条件（公共方法）"""
        query = {}
        
        # 权限检查：构建项目查询条件
        user_projects = user.get("projects", [])
        if user_projects:
            if project_id:
                if project_id not in user_projects:
                    raise HTTPException(
                        status_code=self._get_status_code('HTTP_403_FORBIDDEN', 403),
                        detail="无权限访问此项目的需求"
                    )
                project_options = self._collect_id_options(project_id)
                if project_options:
                    query["project_id"] = project_options[0] if len(project_options) == 1 else {"$in": project_options}
            else:
                project_options = self._collect_id_options(list(user_projects))
                if project_options:
                    query["project_id"] = {"$in": project_options}
        elif project_id:
            project_options = self._collect_id_options(project_id)
            if project_options:
                query["project_id"] = project_options[0] if len(project_options) == 1 else {"$in": project_options}
        
        # 添加筛选条件
        if status:
            query["status"] = {"$in": status}
        if priority:
            query["priority"] = {"$in": priority}
        if requirement_type:
            query["requirement_type"] = {"$in": requirement_type}
        if source:
            query["source"] = {"$in": source}
        if assignee_id:
            assignee_options = self._collect_id_options(assignee_id)
            if assignee_options:
                query["assignee_id"] = assignee_options[0] if len(assignee_options) == 1 else {"$in": assignee_options}
        if reporter_id:
            reporter_options = self._collect_id_options(reporter_id)
            if reporter_options:
                query["reporter_id"] = reporter_options[0] if len(reporter_options) == 1 else {"$in": reporter_options}
        if tags:
            query["tags"] = {"$in": tags}
        if parent_id is not None:
            if parent_id:
                parent_options = self._collect_id_options(parent_id)
                if parent_options:
                    query["parent_id"] = parent_options[0] if len(parent_options) == 1 else {"$in": parent_options}
            else:
                # parent_id 为空字符串或 None 表示只查询顶级需求
                query["$or"] = [{"parent_id": None}, {"parent_id": {"$exists": False}}]
        if created_at_start or created_at_end:
            created_filter: Dict[str, Any] = {}
            if created_at_start:
                created_filter["$gte"] = created_at_start
            if created_at_end:
                created_filter["$lte"] = created_at_end
            query["created_at"] = created_filter
        
        return query
    
    def _is_overdue(self, due_date: Optional[datetime], status_code: str, completed_statuses: List[str] = None) -> bool:
        """
        判断需求是否逾期（同步方法，避免异步调用）
        
        注意：completed_statuses 应该从状态管理系统获取，但由于这是同步方法，
        调用方应该在异步上下文中获取完成状态列表后传入此方法
        """
        if not due_date:
            return False
        if completed_statuses is None:
            # 如果没有提供完成状态列表，记录警告
            # 调用方应该从状态管理系统获取完成状态列表
            logging.warning("_is_overdue 方法未提供完成状态列表，使用空列表作为后备")
            completed_statuses = []
        # 统一转换为大写进行比较
        status_code_upper = status_code.upper() if status_code else ""
        completed_statuses_upper = [s.upper() for s in completed_statuses if s]
        # 如果状态是已完成状态，不算逾期
        if status_code_upper in completed_statuses_upper:
            return False
        return datetime.utcnow() > due_date
    
    async def get_module_states(self, module_name: str):
        """获取模块状态列表 - 从状态管理系统获取"""
        from app.utils.state_utils import get_module_states
        return await get_module_states(self.db, module_name)
    
    async def get_state_code_by_name_keywords(self, module_name: str, keywords: List[str]):
        """通过名称关键词获取状态代码"""
        from app.utils.state_utils import get_state_code_by_name_keywords as get_state_code
        return await get_state_code(self.db, module_name, keywords)
    
    def _parse_datetime_string(self, date_str: Optional[str]) -> Optional[datetime]:
        """解析日期时间字符串"""
        if not date_str:
            return None
        try:
            dt = datetime.fromisoformat(date_str)
            # 如果只有日期部分，时间部分为00:00:00，则设置为当天的23:59:59
            if dt.time() == datetime.min.time():
                dt = dt + timedelta(days=1) - timedelta(microseconds=1)
            return dt
        except ValueError:
            try:
                dt = datetime.strptime(date_str, "%Y-%m-%d")
                dt = dt + timedelta(days=1) - timedelta(microseconds=1)
                return dt
            except ValueError:
                return None
    
    def _parse_tags_string(self, tags_str: Optional[str]) -> Optional[List[str]]:
        """解析标签字符串为列表"""
        if not tags_str:
            return None
        try:
            return [tag.strip() for tag in tags_str.split(",") if tag.strip()]
        except (AttributeError, TypeError):
            return None
    
    def _convert_to_list(self, value: Optional[Any]) -> Optional[List[str]]:
        """将单个值转换为列表"""
        if value is None:
            return None
        if isinstance(value, list):
            return value
        return [str(value)]
    
    def _convert_enum_to_list(self, value: Optional[Any]) -> Optional[List[str]]:
        """将枚举值转换为列表"""
        if value is None:
            return None
        if hasattr(value, 'value'):
            return [value.value]
        if isinstance(value, list):
            return [str(v.value) if hasattr(v, 'value') else str(v) for v in value]
        return [str(value)]
    
    async def _get_version_requirement_ids(self, version_id: Optional[str]) -> Optional[List[ObjectId]]:
        """获取版本关联的需求ID列表"""
        if not version_id:
            return None
        try:
            version_obj_id = self._validate_object_id(version_id, "版本ID")
            requirement_relations = await self.db.version_requirement_relations.find(
                {"version_id": version_obj_id}
            ).limit(10000).to_list(length=10000)
            requirement_ids = [relation["requirement_id"] for relation in requirement_relations]
            return requirement_ids if requirement_ids else None
        except Exception:
            raise HTTPException(
                status_code=self._get_status_code('HTTP_400_BAD_REQUEST', 400),
                detail="Invalid version_id format"
            )
    
    def _convert_requirements_to_dict(self, requirements: List[Any]) -> List[Dict[str, Any]]:
        """将需求对象列表转换为字典列表（支持树形结构）"""
        def convert_to_dict(req):
            """递归转换需求对象为字典"""
            if hasattr(req, 'model_dump'):
                # Pydantic v2
                req_dict = req.model_dump()
            elif hasattr(req, 'dict'):
                # Pydantic v1
                req_dict = req.dict()
            elif isinstance(req, dict):
                req_dict = req
            else:
                req_dict = req
            
            # 递归处理子需求
            if isinstance(req_dict, dict) and 'children' in req_dict and req_dict['children']:
                req_dict['children'] = [convert_to_dict(child) for child in req_dict['children']]
            
            return req_dict
        
        return [convert_to_dict(req) for req in requirements]
    
    async def get_requirements(self, 
                             user: dict,
                             skip: int = 0,
                             limit: int = 100,
                             project_id: Optional[str] = None,
                             status: Optional[Union[str, List[str]]] = None,
                             priority: Optional[Union[str, List[str], Any]] = None,
                             requirement_type: Optional[Union[str, List[str]]] = None,
                             source: Optional[Union[str, List[str]]] = None,
                             assignee_id: Optional[str] = None,
                             reporter_id: Optional[str] = None,
                             tags: Optional[Union[str, List[str]]] = None,
                             is_overdue: Optional[bool] = None,
                             parent_id: Optional[str] = None,
                             sort_by: str = "created_at",
                             sort_order: str = "desc",
                             tree: bool = False,
                             created_at_start: Optional[Union[str, datetime]] = None,
                             created_at_end: Optional[Union[str, datetime]] = None,
                             has_version: Optional[bool] = None,
                             version_id: Optional[str] = None):
        """获取需求列表"""
        try:
            # 参数转换：处理版本ID筛选
            version_requirement_ids = None
            if version_id:
                version_requirement_ids = await self._get_version_requirement_ids(version_id)
                if not version_requirement_ids:
                    # 如果没有关联的需求，返回空列表
                    return []
            
            # 参数转换：处理时间字符串
            created_start_dt = created_at_start
            if isinstance(created_at_start, str):
                created_start_dt = self._parse_datetime_string(created_at_start)
            
            created_end_dt = created_at_end
            if isinstance(created_at_end, str):
                created_end_dt = self._parse_datetime_string(created_at_end)
            
            # 参数转换：处理标签字符串
            tags_list = tags
            if isinstance(tags, str):
                tags_list = self._parse_tags_string(tags)
            
            # 参数转换：处理状态、优先级等枚举值
            status_list = status
            if status and not isinstance(status, list):
                status_list = self._convert_to_list(status)
            
            priority_list = priority
            if priority and not isinstance(priority, list):
                priority_list = self._convert_enum_to_list(priority)
            
            requirement_type_list = requirement_type
            if requirement_type and not isinstance(requirement_type, list):
                requirement_type_list = self._convert_to_list(requirement_type)
            
            source_list = source
            if source and not isinstance(source, list):
                source_list = self._convert_to_list(source)
            
            # 树形结构模式下，忽略分页参数
            if tree:
                skip = 0
                limit = 10000  # 树形结构时获取所有数据
            
            # 构建查询条件（使用公共方法）
            # 注意：参数名 status 会覆盖导入的 http_status，这里使用局部变量 status_filter
            status_filter = status_list  # 重命名避免混淆
            query = self._build_query_filters(
                user=user,
                project_id=project_id,
                status=status_filter,
                priority=priority_list,
                requirement_type=requirement_type_list,
                source=source_list,
                assignee_id=assignee_id,
                reporter_id=reporter_id,
                tags=tags_list,
                parent_id=parent_id if not tree else None,  # 树形模式下不限制parent_id
                created_at_start=created_start_dt,
                created_at_end=created_end_dt
            )
            
            # 如果通过version_id筛选，添加需求ID限制
            if version_requirement_ids:
                query["_id"] = {"$in": version_requirement_ids}
            
            # 构建排序
            sort_order_value = -1 if sort_order == "desc" else 1
            sort_criteria = [(sort_by, sort_order_value)]
            
            # 使用聚合管道优化查询，一次性获取需求、用户、项目和版本信息
            pipeline = [
                {"$match": query},
            ]
            
            # 如果需要筛选版本关联，先添加版本关联查询
            if has_version is not None:
                pipeline.extend([
                    {
                        "$lookup": {
                            "from": "version_requirement_relations",
                            "localField": "_id",
                            "foreignField": "requirement_id",
                            "as": "version_relations"
                        }
                    },
                    {
                        "$addFields": {
                            "has_version_lookup": {
                                "$gt": [
                                    {"$size": "$version_relations"},
                                    0
                                ]
                            }
                        }
                    },
                    {"$match": {"has_version_lookup": has_version}},
                ])
            
            # 添加用户信息关联（负责人）
            pipeline.append({
                "$lookup": {
                    "from": "users",
                    "localField": "assignee_id",
                    "foreignField": "_id",
                    "as": "assignee_info",
                    "pipeline": [
                        {"$project": {
                            "_id": 1,
                            "name": 1,
                            "username": 1,
                            "avatar": 1,
                            "position": 1,
                            "real_name": 1,
                            "nickname": 1,
                            "email": 1
                        }}
                    ]
                }
            })
            
            # 添加用户信息关联（报告人）
            pipeline.append({
                "$lookup": {
                    "from": "users",
                    "localField": "reporter_id",
                    "foreignField": "_id",
                    "as": "reporter_info",
                    "pipeline": [
                        {"$project": {
                            "_id": 1,
                            "name": 1,
                            "username": 1,
                            "avatar": 1,
                            "position": 1,
                            "real_name": 1,
                            "nickname": 1,
                            "email": 1
                        }}
                    ]
                }
            })
            
            # 添加项目信息关联
            pipeline.append({
                "$lookup": {
                    "from": "projects",
                    "localField": "project_id",
                    "foreignField": "_id",
                    "as": "project_info",
                    "pipeline": [
                        {"$project": {
                            "_id": 1,
                            "name": 1,
                            "code": 1
                        }}
                    ]
                }
            })
            
            # 添加版本信息关联
            pipeline.append({
                "$lookup": {
                    "from": "version_requirement_relations",
                    "localField": "_id",
                    "foreignField": "requirement_id",
                    "as": "version_relations_full"
                }
            })
            
            pipeline.append({
                "$lookup": {
                    "from": "versions",
                    "localField": "version_relations_full.version_id",
                    "foreignField": "_id",
                    "as": "versions_full",
                    "pipeline": [
                        {"$project": {
                            "_id": 1,
                            "name": 1,
                            "version_number": 1,
                            "status": 1,
                            "planned_release_date": 1,
                            "actual_release_date": 1
                        }}
                    ]
                }
            })
            
            # 整理数据格式
            pipeline.append({
                "$addFields": {
                    "assignee_doc": {"$arrayElemAt": ["$assignee_info", 0]},
                    "reporter_doc": {"$arrayElemAt": ["$reporter_info", 0]},
                    "project_doc": {"$arrayElemAt": ["$project_info", 0]},
                    "versions_formatted": {
                        "$map": {
                            "input": "$versions_full",
                            "as": "version",
                            "in": {
                                "id": {"$toString": "$$version._id"},
                                "name": "$$version.name",
                                "version_number": "$$version.version_number",
                                "status": "$$version.status",
                                "release_date": {
                                    "$ifNull": [
                                        "$$version.actual_release_date",
                                        "$$version.planned_release_date"
                                    ]
                                }
                            }
                        }
                    }
                }
            })
            
            # 清理不需要的字段
            pipeline.append({
                "$project": {
                    "version_relations": 0,
                    "version_relations_full": 0,
                    "versions_full": 0,
                    "has_version_lookup": 0,
                    "assignee_info": 0,
                    "reporter_info": 0,
                    "project_info": 0
                }
            })
            
            # 排序、分页
            pipeline.extend([
                {"$sort": {sort_by: sort_order_value}},
                {"$skip": skip},
                {"$limit": limit}
            ])
            
            requirements = await self.db.requirements.aggregate(pipeline).to_list(length=limit)
            
            # 如果没有数据，直接返回空列表
            if not requirements:
                return []
            
            # 构建用户和项目映射（从聚合结果中提取）
            users = {}
            projects = {}
            requirement_versions_map = {}
            
            for req in requirements:
                # 提取用户信息
                if req.get("assignee_doc"):
                    assignee_doc = req["assignee_doc"]
                    users[str(assignee_doc["_id"])] = assignee_doc
                if req.get("reporter_doc"):
                    reporter_doc = req["reporter_doc"]
                    users[str(reporter_doc["_id"])] = reporter_doc
                
                # 提取项目信息
                if req.get("project_doc"):
                    project_doc = req["project_doc"]
                    projects[str(project_doc["_id"])] = project_doc
                
                # 提取版本信息
                if req.get("versions_formatted"):
                    requirement_versions_map[str(req["_id"])] = req["versions_formatted"]
            
            # 批量统计关联任务数量（优化性能）
            requirement_ids = [str(req["_id"]) for req in requirements]
            related_tasks_count_map = {}
            if requirement_ids:
                try:
                    # 批量查询所有关联的任务
                    task_counts = await self.db.tasks.aggregate([
                        {
                            "$match": {
                                "requirement_id": {"$in": requirement_ids}
                            }
                        },
                        {
                            "$group": {
                                "_id": "$requirement_id",
                                "count": {"$sum": 1}
                            }
                        }
                    ]).to_list(length=None)
                    
                    # 构建映射
                    for task_count in task_counts:
                        req_id = str(task_count["_id"])
                        related_tasks_count_map[req_id] = task_count["count"]
                except Exception as e:
                    import logging
                    logger = logging.getLogger(__name__)
                    logger.warning(f"统计关联任务数量失败: {str(e)}")
            
            # 处理响应数据
            requirement_responses = []
            
            for req in requirements:
                try:
                    # 计算是否逾期（使用同步方法，避免异步调用）
                    req_status_code = req.get("status", "")
                    # 从状态管理系统获取完成状态列表
                    from app.utils.state_utils import get_completed_state_codes
                    completed_statuses = await get_completed_state_codes(self.db, "requirement")
                    is_overdue_value = self._is_overdue(req.get("due_date"), req_status_code, completed_statuses)
                    
                    # 如果指定了逾期筛选，则过滤
                    if is_overdue is not None and is_overdue_value != is_overdue:
                        continue
                    
                    # 构建响应（从聚合结果中获取用户和项目信息）
                    assignee_doc = req.get("assignee_doc")
                    reporter_doc = req.get("reporter_doc")
                    project_doc = req.get("project_doc")
                    req_id_str = str(req["_id"])
                    
                    response = RequirementResponse(
                        id=req_id_str,
                        title=req.get("title", ""),
                        description=req.get("description"),
                        requirement_type=req.get("requirement_type", ""),
                        status=req_status_code,
                        priority=req.get("priority", ""),
                        source=req.get("source", ""),
                        project_id=str(req["project_id"]),
                        project_name=project_doc.get("name") if project_doc else None,
                        assignee_id=str(req["assignee_id"]) if req.get("assignee_id") else None,
                        assignee_name=self._get_user_display_name(assignee_doc),
                        reporter_id=str(req["reporter_id"]) if req.get("reporter_id") else None,
                        reporter_name=self._get_user_display_name(reporter_doc),
                        acceptance_criteria=req.get("acceptance_criteria"),
                        business_value=req.get("business_value"),
                        estimated_effort=req.get("estimated_effort"),
                        actual_effort=req.get("actual_effort"),
                        start_date=req.get("start_date"),
                        due_date=req.get("due_date"),
                        tags=req.get("tags", []),
                        comments=[RequirementComment(**{**c, "status": c.get("status") or "published"}) for c in req.get("comments", [])] if req.get("comments") else [],
                        attachments=self._format_attachments(req.get("attachments", [])),
                        dependencies=req.get("dependencies", []),
                        watchers=[str(w) for w in req.get("watchers", [])] if req.get("watchers") else [],
                        versions=requirement_versions_map.get(req_id_str, []),
                        created_at=req.get("created_at"),
                        updated_at=req.get("updated_at"),
                        approved_at=req.get("approved_at"),
                        implemented_at=req.get("implemented_at"),
                        parent_id=str(req["parent_id"]) if req.get("parent_id") else None,
                        sort_order=req.get("sort_order", 0),
                        children=[],
                        children_count=0,
                        is_overdue=is_overdue_value,
                        progress=0.0,  # 简化处理，实际应该计算
                        related_tasks_count=related_tasks_count_map.get(req_id_str, 0)
                    )
                    
                    requirement_responses.append(response)
                except Exception as req_error:
                    # 记录单个需求处理错误，但继续处理其他需求
                    import logging
                    logger = logging.getLogger(__name__)
                    logger.warning(f"处理需求 {req.get('_id')} 时出错: {str(req_error)}")
                    continue
            
            # 如果启用树形结构，构建树
            if tree:
                return self._build_requirement_tree(requirement_responses)
            
            return requirement_responses
            
        except HTTPException:
            raise
        except Exception as e:
            import logging
            import traceback
            logger = logging.getLogger(__name__)
            logger.error(f"获取需求列表失败: {str(e)}", exc_info=True)
            raise HTTPException(
                status_code=self._get_status_code('HTTP_500_INTERNAL_SERVER_ERROR', 500),
                detail=f"获取需求列表失败: {str(e)}"
            )
    
    async def get_requirements_as_dict(self, 
                                      user: dict,
                                      page: int = 1,
                                      size: int = 20,
                                      **kwargs) -> Dict[str, Any]:
        """获取需求列表并返回字典格式（包含分页信息）"""
        try:
            # 计算skip和limit
            skip = (page - 1) * size
            limit = size
            
            # 树形结构模式下，忽略分页参数
            if kwargs.get('tree', False):
                skip = 0
                limit = 10000
            
            # 调用get_requirements方法
            requirements = await self.get_requirements(
                user=user,
                skip=skip,
                limit=limit,
                **kwargs
            )
            
            # 转换为字典格式
            requirements_dict = self._convert_requirements_to_dict(requirements)
            
            # 获取总数
            total = await self.get_requirements_count(
                user=user,
                **{k: v for k, v in kwargs.items() if k not in ['tree', 'skip', 'limit', 'sort_by', 'sort_order']}
            )
            
            return {
                "items": requirements_dict,
                "total": total,
                "page": page,
                "size": size
            }
        except HTTPException:
            raise
        except Exception as e:
            import logging
            import traceback
            logger = logging.getLogger(__name__)
            logger.error(f"获取需求列表失败: {str(e)}", exc_info=True)
            raise HTTPException(
                status_code=self._get_status_code('HTTP_500_INTERNAL_SERVER_ERROR', 500),
                detail=f"获取需求列表失败: {str(e)}"
            )
    
    async def get_requirements_count(self, 
                                   user: dict,
                                   project_id: Optional[str] = None,
                                   status: Optional[Union[str, List[str]]] = None,
                                   priority: Optional[Union[str, List[str], Any]] = None,
                                   requirement_type: Optional[Union[str, List[str]]] = None,
                                   source: Optional[Union[str, List[str]]] = None,
                                   assignee_id: Optional[str] = None,
                                   reporter_id: Optional[str] = None,
                                   tags: Optional[Union[str, List[str]]] = None,
                                   is_overdue: Optional[bool] = None,
                                   parent_id: Optional[str] = None,
                                   created_at_start: Optional[Union[str, datetime]] = None,
                                   created_at_end: Optional[Union[str, datetime]] = None,
                                   has_version: Optional[bool] = None,
                                   version_id: Optional[str] = None):
        """获取需求总数"""
        try:
            # 参数转换：处理版本ID筛选
            version_requirement_ids = None
            if version_id:
                version_requirement_ids = await self._get_version_requirement_ids(version_id)
                if not version_requirement_ids:
                    return 0
            
            # 参数转换：处理时间字符串
            created_start_dt = created_at_start
            if isinstance(created_at_start, str):
                created_start_dt = self._parse_datetime_string(created_at_start)
            
            created_end_dt = created_at_end
            if isinstance(created_at_end, str):
                created_end_dt = self._parse_datetime_string(created_at_end)
            
            # 参数转换：处理标签字符串
            tags_list = tags
            if isinstance(tags, str):
                tags_list = self._parse_tags_string(tags)
            
            # 参数转换：处理状态、优先级等枚举值
            status_list = status
            if status and not isinstance(status, list):
                status_list = self._convert_to_list(status)
            
            priority_list = priority
            if priority and not isinstance(priority, list):
                priority_list = self._convert_enum_to_list(priority)
            
            requirement_type_list = requirement_type
            if requirement_type and not isinstance(requirement_type, list):
                requirement_type_list = self._convert_to_list(requirement_type)
            
            source_list = source
            if source and not isinstance(source, list):
                source_list = self._convert_to_list(source)
            
            # 构建查询条件（使用公共方法）
            query = self._build_query_filters(
                user=user,
                project_id=project_id,
                status=status_list,
                priority=priority_list,
                requirement_type=requirement_type_list,
                source=source_list,
                assignee_id=assignee_id,
                reporter_id=reporter_id,
                tags=tags_list,
                parent_id=parent_id,
                created_at_start=created_start_dt,
                created_at_end=created_end_dt
            )
            
            # 如果通过version_id筛选，添加需求ID限制
            if version_requirement_ids:
                query["_id"] = {"$in": version_requirement_ids}
            
            # 如果需要筛选逾期，需要先查询所有符合条件的需求再过滤
            if is_overdue is not None:
                requirements = await self.db.requirements.find(query).to_list(length=None)
                
                filtered_requirements = []
                for req in requirements:
                    status_code = req.get("status", "")
                    # 从状态管理系统获取完成状态列表
                    from app.utils.state_utils import get_completed_state_codes
                    completed_statuses = await get_completed_state_codes(self.db, "requirement")
                    overdue = self._is_overdue(req.get("due_date"), status_code, completed_statuses)
                    if overdue == is_overdue:
                        filtered_requirements.append(req)
                
                if has_version is not None:
                    if not filtered_requirements:
                        return 0
                    requirement_ids = [req["_id"] for req in filtered_requirements]
                    relations = await self.db.version_requirement_relations.find(
                        {"requirement_id": {"$in": requirement_ids}}
                    ).to_list(length=None)
                    assigned_ids = {rel.get("requirement_id") for rel in relations if rel.get("requirement_id")}
                    if has_version:
                        return len([req_id for req_id in requirement_ids if req_id in assigned_ids])
                    return len([req_id for req_id in requirement_ids if req_id not in assigned_ids])
                return len(filtered_requirements)
            else:
                if has_version is not None:
                    requirement_docs = await self.db.requirements.find(query, {"_id": 1}).to_list(length=None)
                    if not requirement_docs:
                        return 0
                    requirement_ids = [doc["_id"] for doc in requirement_docs]
                    relations = await self.db.version_requirement_relations.find(
                        {"requirement_id": {"$in": requirement_ids}}
                    ).to_list(length=None)
                    assigned_ids = {rel.get("requirement_id") for rel in relations if rel.get("requirement_id")}
                    if has_version:
                        return len([req_id for req_id in requirement_ids if req_id in assigned_ids])
                    return len([req_id for req_id in requirement_ids if req_id not in assigned_ids])
                return await self.db.requirements.count_documents(query)
                
        except HTTPException:
            raise
        except Exception as e:
            raise HTTPException(
                status_code=self._get_status_code('HTTP_500_INTERNAL_SERVER_ERROR', 500),
                detail=f"获取需求总数失败: {str(e)}"
            )
    
    async def get_requirement(self, user: dict, requirement_id: str) -> RequirementResponse:
        """获取需求详情"""
        try:
            # 验证需求ID格式
            requirement_id_obj = self._validate_object_id(requirement_id, "需求ID")
            
            # 查询需求
            requirement = await self.db.requirements.find_one({"_id": requirement_id_obj})
            if not requirement:
                raise HTTPException(
                    status_code=self._get_status_code('HTTP_404_NOT_FOUND', 404),
                        detail="需求不存在"
                    )
            
            # 权限检查
            project_id = str(requirement["project_id"])
            user_projects = user.get("projects", [])
            if user_projects and project_id not in user_projects:
                raise HTTPException(
                    status_code=self._get_status_code('HTTP_403_FORBIDDEN', 403),
                    detail="无权限访问此需求"
                )
            
            # 获取用户信息
            user_ids = []
            if requirement.get("assignee_id"):
                user_ids.append(requirement["assignee_id"])
            if requirement.get("reporter_id"):
                user_ids.append(requirement["reporter_id"])
            if requirement.get("updated_by"):
                user_ids.append(requirement["updated_by"])
            
            users = {}
            if user_ids:
                async for user_doc in self.db.users.find(
                    {"_id": {"$in": user_ids}},
                    {"_id": 1, "name": 1, "username": 1, "avatar": 1, "position": 1, "real_name": 1, "nickname": 1, "email": 1}
                ):
                    users[str(user_doc["_id"])] = user_doc
            
            # 获取项目信息
            project = await self.db.projects.find_one({"_id": requirement["project_id"]})
            
            # 获取关联的版本信息
            requirement_versions = await self._get_requirement_versions(requirement_id_obj)
            
            # 统计关联任务数量
            related_tasks_count = 0
            try:
                related_tasks_count = await self.db.tasks.count_documents({
                    "requirement_id": requirement_id
                })
            except Exception as e:
                import logging
                logger = logging.getLogger(__name__)
                logger.warning(f"统计需求 {requirement_id} 的关联任务数量失败: {str(e)}")
            
            # 计算是否逾期（使用同步方法）
            status_code = requirement.get("status", "")
            is_overdue = self._is_overdue(requirement.get("due_date"), status_code)
            
            # 构建响应
            assignee_doc = users.get(str(requirement["assignee_id"])) if requirement.get("assignee_id") else None
            reporter_doc = users.get(str(requirement["reporter_id"])) if requirement.get("reporter_id") else None
            updated_by_doc = users.get(str(requirement["updated_by"])) if requirement.get("updated_by") else None
            
            response = RequirementResponse(
                id=str(requirement["_id"]),
                title=requirement["title"],
                description=requirement.get("description"),
                requirement_type=requirement["requirement_type"],
                status=requirement.get("status", ""),
                priority=requirement["priority"],
                source=requirement["source"],
                project_id=str(requirement["project_id"]),
                project_name=project.get("name") if project else None,
                assignee_id=str(requirement["assignee_id"]) if requirement.get("assignee_id") else None,
                assignee_name=self._get_user_display_name(assignee_doc),
                reporter_id=str(requirement["reporter_id"]),
                reporter_name=self._get_user_display_name(reporter_doc),
                updated_by_id=str(requirement["updated_by"]) if requirement.get("updated_by") else None,
                updated_by_name=self._get_user_display_name(updated_by_doc),
                acceptance_criteria=requirement.get("acceptance_criteria"),
                business_value=requirement.get("business_value"),
                estimated_effort=requirement.get("estimated_effort"),
                actual_effort=requirement.get("actual_effort"),
                start_date=requirement.get("start_date"),
                due_date=requirement.get("due_date"),
                tags=requirement.get("tags", []),
                comments=[RequirementComment(**{**c, "status": c.get("status") or "published"}) for c in requirement.get("comments", [])],
                attachments=self._format_attachments(requirement.get("attachments", [])),
                dependencies=requirement.get("dependencies", []),
                watchers=[str(w) for w in requirement.get("watchers", [])],
                versions=requirement_versions,
                created_at=requirement.get("created_at"),
                updated_at=requirement.get("updated_at"),
                approved_at=requirement.get("approved_at"),
                implemented_at=requirement.get("implemented_at"),
                parent_id=str(requirement["parent_id"]) if requirement.get("parent_id") else None,
                sort_order=requirement.get("sort_order", 0),
                children=[],
                children_count=await self.db.requirements.count_documents({
                    "parent_id": requirement_id_obj
                }),
                is_overdue=is_overdue,
                progress=0.0,  # 简化处理，实际应该计算
                related_tasks_count=related_tasks_count
            )
            
            return response
            
        except HTTPException:
            raise
        except Exception as e:
            raise HTTPException(
                status_code=self._get_status_code('HTTP_500_INTERNAL_SERVER_ERROR', 500),
                detail=f"获取需求详情失败: {str(e)}"
            )
    
    async def create_requirement(self, user: dict, requirement_data: dict) -> RequirementResponse:
        """创建需求"""
        try:
            # 权限检查
            project_id_raw = requirement_data.get("project_id")
            project_id_str = str(project_id_raw)
            await self._check_project_permission(user, project_id_str, "requirement:create")
            
            # 验证项目是否存在
            project_id_obj = self._validate_object_id(project_id_raw, "项目ID")
            project = await self.db.projects.find_one({"_id": project_id_obj})
            if not project:
                raise HTTPException(
                    status_code=self._get_status_code('HTTP_404_NOT_FOUND', 404),
                        detail="项目不存在"
                    )
            
            # 验证负责人是否存在
            assignee_id_obj = None
            if requirement_data.get("assignee_id"):
                assignee_id_obj = self._to_object_id(requirement_data["assignee_id"])
                if assignee_id_obj:
                    assignee = await self.db.users.find_one({"_id": assignee_id_obj})
                    if not assignee:
                        raise HTTPException(
                            status_code=self._get_status_code('HTTP_404_NOT_FOUND', 404),
                            detail="指定的负责人不存在"
                        )
                else:
                    raise HTTPException(
                        status_code=self._get_status_code('HTTP_400_BAD_REQUEST', 400),
                        detail="无效的负责人ID"
                    )
            
            # 构建需求数据（确保包含所有必要字段）
            requirement_data = requirement_data.copy()

            assigned_status_code = await self._get_state_code_by_alias("requirement", ["assigned", "已分配"])
            status_raw = requirement_data.get("status")
            status_value = str(status_raw or "").strip()
            status_lower = status_value.lower()
            if not status_lower:
                # 从状态管理系统获取默认状态
                from app.core.state_validator import get_state_validator
                state_validator = get_state_validator(self.db)
                default_status = await state_validator.get_default_state_code("requirement")
                if default_status:
                    status_lower = default_status.lower()
                    status_value = default_status
                    requirement_data["status"] = default_status
                else:
                    # 如果无法获取默认状态，记录警告并抛出异常
                    logging.warning("无法从状态管理系统获取需求的默认状态")
                    raise HTTPException(
                        status_code=self._get_status_code('HTTP_500_INTERNAL_SERVER_ERROR', 500),
                        detail="无法获取默认状态，请检查状态管理系统配置"
                    )

            if assignee_id_obj is None and not await self._status_allows_unassigned(status_lower):
                raise HTTPException(
                    status_code=self._get_status_code('HTTP_400_BAD_REQUEST', 400),
                    detail=f"状态“{status_value}”下必须指定负责人"
                )

            if assigned_status_code:
                assigned_lower = assigned_status_code.lower()
                if assignee_id_obj and await self._status_allows_unassigned(status_lower):
                    requirement_data["status"] = assigned_status_code

            # 规范化ID字段
            reporter_id_obj = self._validate_object_id(requirement_data.get("reporter_id"), "报告人ID")
            requirement_data["project_id"] = project_id_obj
            requirement_data["reporter_id"] = reporter_id_obj
            requirement_data["assignee_id"] = assignee_id_obj if assignee_id_obj else None
            
            # 处理 parent_id
            if requirement_data.get("parent_id"):
                parent_id_obj = self._to_object_id(requirement_data["parent_id"])
                if parent_id_obj:
                    # 验证父需求是否存在且属于同一项目
                    parent_req = await self.db.requirements.find_one({"_id": parent_id_obj})
                    if not parent_req:
                        raise HTTPException(
                            status_code=self._get_status_code('HTTP_404_NOT_FOUND', 404),
                            detail="父需求不存在"
                        )
                    if str(parent_req["project_id"]) != project_id_str:
                        raise HTTPException(
                            status_code=self._get_status_code('HTTP_400_BAD_REQUEST', 400),
                            detail="父需求必须属于同一项目"
                        )
                    requirement_data["parent_id"] = parent_id_obj
                else:
                    requirement_data["parent_id"] = None
            else:
                requirement_data["parent_id"] = None
            
            requirement_data.update({
                "created_at": datetime.utcnow(),
                "updated_at": datetime.utcnow(),
                "comments": [],
                "attachments": [],
                "dependencies": [],
                "watchers": [],
                "sort_order": requirement_data.get("sort_order", 0)
            })
            
            # 创建需求
            result = await self.db.requirements.insert_one(requirement_data)
            
            # 获取创建的需求
            created_requirement = await self.db.requirements.find_one({"_id": result.inserted_id})
            
            # 构建响应
            return await self.get_requirement(user, str(result.inserted_id))
            
        except HTTPException:
            raise
        except Exception as e:
            raise HTTPException(
                status_code=self._get_status_code('HTTP_500_INTERNAL_SERVER_ERROR', 500),
                detail=f"创建需求失败: {str(e)}"
            )
    
    async def update_requirement(self, user: dict, requirement_id: str, update_data: dict) -> RequirementResponse:
        """更新需求"""
        try:
            # 验证需求ID格式
            requirement_id_obj = self._validate_object_id(requirement_id, "需求ID")
            
            # 查询需求
            requirement = await self.db.requirements.find_one({"_id": requirement_id_obj})
            if not requirement:
                raise HTTPException(
                    status_code=self._get_status_code('HTTP_404_NOT_FOUND', 404),
                        detail="需求不存在"
                    )
            
            # 权限检查
            project_id = str(requirement["project_id"])
            await self._check_project_permission(user, project_id, "requirement:update")
            
            # 处理项目更新
            old_project_id = str(requirement["project_id"])
            project_changed = False
            if "project_id" in update_data:
                new_project_id = update_data.get("project_id")
                if new_project_id:
                    project_id_obj = self._validate_object_id(new_project_id, "项目ID")
                    new_project_id_str = str(project_id_obj)
                    await self._check_project_permission(user, new_project_id_str, "requirement:update")
                    project = await self.db.projects.find_one({"_id": project_id_obj})
                    if not project:
                        raise HTTPException(
                            status_code=self._get_status_code('HTTP_404_NOT_FOUND', 404),
                            detail="项目不存在"
                        )
                    # 检查项目是否真的改变了
                    if new_project_id_str != old_project_id:
                        project_changed = True
                    update_data["project_id"] = project_id_obj
                else:
                    update_data.pop("project_id", None)
            
            # 处理 parent_id 更新
            if "parent_id" in update_data:
                parent_id = update_data.get("parent_id")
                if parent_id:
                    # 防止循环引用：检查是否将自己设为父级
                    if parent_id == requirement_id:
                        raise HTTPException(
                            status_code=self._get_status_code('HTTP_400_BAD_REQUEST', 400),
                            detail="不能将自己设为父需求"
                        )
                    
                    # 验证父需求是否存在且属于同一项目
                    parent_id_obj = self._to_object_id(parent_id)
                    if parent_id_obj:
                        parent_req = await self.db.requirements.find_one({"_id": parent_id_obj})
                        if not parent_req:
                            raise HTTPException(
                                status_code=self._get_status_code('HTTP_404_NOT_FOUND', 404),
                                detail="父需求不存在"
                            )
                        if str(parent_req["project_id"]) != project_id:
                            raise HTTPException(
                                status_code=self._get_status_code('HTTP_400_BAD_REQUEST', 400),
                                detail="父需求必须属于同一项目"
                            )
                        # 检查是否会产生循环引用（父需求的祖先不能是自己）
                        current_parent_id = parent_id_obj
                        depth = 0
                        while current_parent_id and depth < 100:  # 防止无限循环
                            if current_parent_id == requirement_id_obj:
                                raise HTTPException(
                                    status_code=self._get_status_code('HTTP_400_BAD_REQUEST', 400),
                                    detail="不能将子需求设为父需求，会产生循环引用"
                                )
                            parent_req = await self.db.requirements.find_one({"_id": current_parent_id})
                            if not parent_req or not parent_req.get("parent_id"):
                                break
                            current_parent_id = parent_req["parent_id"]
                            depth += 1
                        
                        update_data["parent_id"] = parent_id_obj
                    else:
                        update_data["parent_id"] = None
                else:
                    update_data["parent_id"] = None
            
            assigned_status_code = await self._get_state_code_by_alias("requirement", ["assigned", "已分配"])
            assigned_lower = assigned_status_code.lower() if assigned_status_code else None

            current_status_lower = (requirement.get("status") or "").lower()
            effective_assignee_obj = requirement.get("assignee_id")
            assignee_updated = False

            # 验证负责人是否存在
            if "assignee_id" in update_data:
                assignee_id = update_data.get("assignee_id")
                assignee_updated = True
                if assignee_id:
                    assignee_id_obj = self._to_object_id(assignee_id)
                    if assignee_id_obj:
                        assignee = await self.db.users.find_one({"_id": assignee_id_obj})
                        if not assignee:
                            raise HTTPException(
                                status_code=self._get_status_code('HTTP_404_NOT_FOUND', 404),
                                detail="指定的负责人不存在"
                            )
                        update_data["assignee_id"] = assignee_id_obj
                        effective_assignee_obj = assignee_id_obj
                    else:
                        raise HTTPException(
                            status_code=self._get_status_code('HTTP_400_BAD_REQUEST', 400),
                            detail="无效的负责人ID"
                        )
                else:
                    update_data["assignee_id"] = None
                    effective_assignee_obj = None
            else:
                effective_assignee_obj = requirement.get("assignee_id")
            
            # 验证报告人是否存在
            if "reporter_id" in update_data:
                reporter_id = update_data.get("reporter_id")
                if reporter_id:
                    reporter_id_obj = self._to_object_id(reporter_id)
                    if reporter_id_obj:
                        reporter = await self.db.users.find_one({"_id": reporter_id_obj})
                        if not reporter:
                            raise HTTPException(
                                status_code=self._get_status_code('HTTP_404_NOT_FOUND', 404),
                                detail="指定的报告人不存在"
                            )
                        update_data["reporter_id"] = reporter_id_obj
                    else:
                        raise HTTPException(
                            status_code=self._get_status_code('HTTP_400_BAD_REQUEST', 400),
                            detail="无效的报告人ID"
                        )
                else:
                    update_data["reporter_id"] = None

            status_updated = False
            status_value_after_update = requirement.get("status", "")
            if "status" in update_data:
                status_updated = True
                status_raw = update_data.get("status")
                if status_raw is not None:
                    status_value_after_update = str(status_raw)
                    update_data["status"] = status_value_after_update
                else:
                    update_data["status"] = ""
                    status_value_after_update = ""

            status_value_lower = (status_value_after_update or "").lower()
            if not status_value_lower:
                # 从状态管理系统获取默认状态
                from app.core.state_validator import get_state_validator
                state_validator = get_state_validator(self.db)
                default_status = await state_validator.get_default_state_code("requirement")
                if default_status:
                    status_value_lower = default_status.lower()
                    status_value_after_update = default_status
                else:
                    # 如果无法获取默认状态，记录警告
                    logging.warning("无法从状态管理系统获取需求的默认状态")
                    status_value_lower = ""

            if assigned_status_code:
                if status_updated and status_value_lower == assigned_lower and not effective_assignee_obj:
                    raise HTTPException(
                        status_code=self._get_status_code('HTTP_400_BAD_REQUEST', 400),
                        detail="状态为已分配时必须指定负责人"
                    )

                if effective_assignee_obj and assigned_lower:
                    should_auto_assign = False
                    if not status_updated:
                        should_auto_assign = await self._status_allows_unassigned(current_status_lower or "")
                    elif status_value_lower == current_status_lower:
                        should_auto_assign = await self._status_allows_unassigned(current_status_lower or "")

                    if assignee_updated and should_auto_assign:
                        update_data["status"] = assigned_status_code
                        status_value_after_update = assigned_status_code
                        status_value_lower = assigned_lower

            if not effective_assignee_obj and not self._status_allows_unassigned(status_value_lower):
                raise HTTPException(
                    status_code=self._get_status_code('HTTP_400_BAD_REQUEST', 400),
                    detail=f"状态“{status_value_after_update or requirement.get('status') or '当前状态'}”下必须指定负责人"
                )
            
            # 构建更新数据（确保包含更新时间和更新人）
            update_data = update_data.copy()
            update_data["updated_at"] = datetime.utcnow()
            # 记录更新人
            current_user_id = ObjectId(user.get("user_id"))
            update_data["updated_by"] = current_user_id
            
            # 特殊处理状态变更
            if "status" in update_data:
                # 获取特定状态代码
                approved_status_code = await self.get_state_code_by_name_keywords("requirement", ["已批准", "批准", "approved"])
                implemented_status_code = await self.get_state_code_by_name_keywords("requirement", ["已实现", "实现", "implemented"])
                
                if approved_status_code and update_data["status"] == approved_status_code:
                    update_data["approved_at"] = datetime.utcnow()
                elif implemented_status_code and update_data["status"] == implemented_status_code:
                    update_data["implemented_at"] = datetime.utcnow()
            
            # 更新需求
            await self.db.requirements.update_one(
                {"_id": requirement_id_obj},
                {"$set": update_data}
            )
            
            # 如果项目改变了，需要移除不属于新项目的版本关联
            if project_changed and "project_id" in update_data:
                new_project_id_obj = update_data["project_id"]
                # 查找所有关联的版本
                version_relations = await self.db.version_requirement_relations.find({
                    "requirement_id": requirement_id_obj
                }).to_list(length=None)
                
                # 移除不属于新项目的版本关联
                removed_count = 0
                for relation in version_relations:
                    version_id = relation.get("version_id")
                    if version_id:
                        version = await self.db.versions.find_one({"_id": version_id})
                        if version and version.get("project_id") != new_project_id_obj:
                            await self.db.version_requirement_relations.delete_one({
                                "_id": relation["_id"]
                            })
                            removed_count += 1
                
                if removed_count > 0:
                    logging.getLogger(__name__).info(f"需求 {requirement_id} 项目变更，已移除 {removed_count} 个不匹配的版本关联")
            
            # 返回更新后的需求
            return await self.get_requirement(user, requirement_id)
            
        except HTTPException:
            raise
        except Exception as e:
            raise HTTPException(
                status_code=self._get_status_code('HTTP_500_INTERNAL_SERVER_ERROR', 500),
                detail=f"更新需求失败: {str(e)}"
            )
    
    async def delete_requirement(self, user: dict, requirement_id: str) -> dict:
        """删除需求"""
        try:
            # 验证需求ID格式
            requirement_id_obj = self._validate_object_id(requirement_id, "需求ID")
            
            # 查询需求
            requirement = await self.db.requirements.find_one({"_id": requirement_id_obj})
            if not requirement:
                raise HTTPException(
                    status_code=self._get_status_code('HTTP_404_NOT_FOUND', 404),
                    detail="需求不存在"
                )
            
            # 权限检查
            project_id = str(requirement["project_id"])
            await self._check_project_permission(user, project_id, "requirement:delete")
            
            # 删除需求
            result = await self.db.requirements.delete_one({"_id": requirement_id_obj})
            
            if result.deleted_count == 0:
                raise HTTPException(
                    status_code=self._get_status_code('HTTP_404_NOT_FOUND', 404),
                    detail="删除失败，需求不存在"
                )
            
            return {"message": "需求删除成功"}
            
        except HTTPException:
            raise
        except Exception as e:
            raise HTTPException(
                status_code=self._get_status_code('HTTP_500_INTERNAL_SERVER_ERROR', 500),
                detail=f"删除需求失败: {str(e)}"
            )
    
    async def update_requirement_status(self, user: dict, requirement_id: str, status_update: RequirementStatusUpdate) -> RequirementResponse:
        """更新需求状态"""
        try:
            # 验证需求ID格式
            requirement_id_obj = self._validate_object_id(requirement_id, "需求ID")
            
            # 查询需求
            requirement = await self.db.requirements.find_one({"_id": requirement_id_obj})
            if not requirement:
                raise HTTPException(
                    status_code=self._get_status_code('HTTP_404_NOT_FOUND', 404),
                    detail="需求不存在"
                )
            
            # 权限检查
            project_id = str(requirement["project_id"])
            await self._check_project_permission(user, project_id, "requirement:update")
            
            # 获取当前状态
            current_status = requirement.get("status")
            
            # 统一转换为大写（因为状态代码在数据库中存储为大写）
            target_status = status_update.status.upper() if status_update.status else status_update.status
            current_status_upper = current_status.upper() if current_status else current_status
            
            # 验证状态流转
            from app.core.state_validator import get_state_validator
            state_validator = get_state_validator(self.db)
            
            # 验证目标状态是否有效（必须启用）
            if not await state_validator.validate_state_code("requirement", target_status):
                # 获取所有可用状态以便提供更好的错误消息
                try:
                    available_states = await state_validator.get_valid_state_codes("requirement")
                    available_states_str = ", ".join(available_states) if available_states else "无"
                    raise HTTPException(
                        status_code=self._get_status_code('HTTP_400_BAD_REQUEST', 400),
                        detail=f"状态 '{status_update.status}' 无效或已被禁用，无法更新到此状态。可用状态：{available_states_str}"
                    )
                except Exception:
                    raise HTTPException(
                        status_code=self._get_status_code('HTTP_400_BAD_REQUEST', 400),
                        detail=f"状态 '{status_update.status}' 无效或已被禁用，无法更新到此状态"
                    )
            
            # 如果状态没有改变，直接返回当前需求
            if current_status_upper == target_status:
                return await self.get_requirement(user, requirement_id)
            
            # 验证状态流转是否合法
            if not await state_validator.validate_state_transition("requirement", current_status_upper, target_status):
                # 获取状态信息以便提供更好的错误消息
                from_state_name = current_status or "未设置"
                to_state_name = status_update.status
                
                try:
                    from_state_info = await state_validator.get_state_info("requirement", current_status_upper) if current_status_upper else None
                    to_state_info = await state_validator.get_state_info("requirement", target_status)
                    
                    if from_state_info:
                        from_state_name = from_state_info.get("name", current_status)
                    if to_state_info:
                        to_state_name = to_state_info.get("name", status_update.status)
                except Exception:
                    pass
                
                raise HTTPException(
                    status_code=self._get_status_code('HTTP_400_BAD_REQUEST', 400),
                    detail=f"不允许从状态「{from_state_name}」流转到「{to_state_name}」，请检查状态流转规则"
                )
            
            # 构建更新数据（使用统一的大写格式）
            update_data = {
                "status": target_status,
                "updated_at": datetime.utcnow()
            }
            
            # 处理特定状态的时间戳（使用统一的大写格式进行比较）
            approved_status_code = await self.get_state_code_by_name_keywords("requirement", ["已批准", "批准", "approved"])
            implemented_status_code = await self.get_state_code_by_name_keywords("requirement", ["已实现", "实现", "implemented"])
            
            # 统一转换为大写进行比较
            approved_status_code_upper = approved_status_code.upper() if approved_status_code else None
            implemented_status_code_upper = implemented_status_code.upper() if implemented_status_code else None
            
            if approved_status_code_upper and target_status == approved_status_code_upper:
                update_data["approved_at"] = datetime.utcnow()
            elif implemented_status_code_upper and target_status == implemented_status_code_upper:
                update_data["implemented_at"] = datetime.utcnow()
            
            # 构建更新操作
            update_operations = {"$set": update_data}
            
            # 如果有状态变更说明，添加评论
            if status_update.comment:
                comment = {
                    "id": str(ObjectId()),
                    "user_id": user["user_id"],
                    "user_name": user["username"],
                    "content": status_update.comment,
                    "parent_id": None,
                    "status": "published",
                    "is_deleted": False,
                    "created_at": datetime.utcnow(),
                    "updated_at": None,
                    "deleted_at": None
                }
                update_operations["$push"] = {"comments": comment}
            
            # 更新需求状态
            await self.db.requirements.update_one(
                {"_id": requirement_id_obj},
                update_operations
            )
            
            # 触发版本状态流转检查（异步，不阻塞响应）
            try:
                from app.utils.version_transition_trigger import trigger_version_transition_check
                import asyncio
                asyncio.create_task(trigger_version_transition_check(self.db, "requirement", requirement_id_obj))
            except Exception as e:
                import logging
                logger = logging.getLogger(__name__)
                logger.warning(f"触发版本状态流转检查失败: {str(e)}")
            
            # 返回更新后的需求
            return await self.get_requirement(user, requirement_id)
            
        except HTTPException:
            raise
        except Exception as e:
            raise HTTPException(
                status_code=self._get_status_code('HTTP_500_INTERNAL_SERVER_ERROR', 500),
                detail=f"更新需求状态失败: {str(e)}"
            )
    
    async def get_requirement_board(self, user: dict, project_id: str) -> dict:
        """获取需求看板数据"""
        try:
            # 验证项目ID格式
            project_id_obj = self._validate_object_id(project_id, "项目ID")
            
            # 权限检查
            await self._check_project_permission(user, project_id, "requirement:view")
            
            # 查询项目
            project = await self.db.projects.find_one({"_id": project_id_obj})
            if not project:
                raise HTTPException(
                    status_code=self._get_status_code('HTTP_404_NOT_FOUND', 404),
                    detail="项目不存在"
                )
            
            # 查询项目的所有需求
            requirements = await self.db.requirements.find({"project_id": project_id_obj}).to_list(length=None)
            
            # 获取用户信息
            user_ids = list(set([req.get("assignee_id") for req in requirements if req.get("assignee_id")]))
            users = {}
            if user_ids:
                async for user_doc in self.db.users.find(
                    {"_id": {"$in": user_ids}},
                    {"_id": 1, "name": 1, "username": 1, "avatar": 1, "position": 1, "real_name": 1, "nickname": 1, "email": 1}
                ):
                    users[str(user_doc["_id"])] = self._get_user_display_name(user_doc)
            
            # 获取所有需求状态
            requirement_states = await self.get_module_states("requirement")
            
            # 按状态分组需求
            columns = {}
            for state in requirement_states:
                if state.is_visible:
                    columns[state.code] = RequirementBoardColumn(
                        status=state.code,
                        title=state.name,
                        requirements=[],
                        requirement_count=0
                    )
            
            # 处理需求数据（使用同步方法优化）
            for req in requirements:
                # 计算是否逾期（使用同步方法）
                status_code = req.get("status", "")
                # 从状态管理系统获取完成状态列表
                from app.utils.state_utils import get_completed_state_codes
                completed_statuses = await get_completed_state_codes(self.db, "requirement")
                is_overdue = self._is_overdue(req.get("due_date"), status_code, completed_statuses)
                
                # 计算进度（根据状态在列表中的位置）
                progress = 0.0
                status_code = req["status"]
                # 找到状态在列表中的索引，根据索引计算进度
                status_index = next((i for i, s in enumerate(requirement_states) if s.code == status_code), -1)
                if status_index >= 0:
                    progress = min(100.0, (status_index + 1) * (100.0 / len(requirement_states)))
                
                requirement_summary = RequirementSummary(
                    id=str(req["_id"]),
                    title=req["title"],
                    status=req["status"],
                    priority=req["priority"],
                    assignee_name=users.get(str(req["assignee_id"])) if req.get("assignee_id") else None,
                    due_date=req.get("due_date"),
                    is_overdue=is_overdue,
                    progress=progress
                )
                
                # 确保状态存在于columns中
                if status_code in columns:
                    columns[status_code].requirements.append(requirement_summary)
                    columns[status_code].requirement_count += 1
            
            # 处理 Pydantic v1 和 v2 的兼容性
            columns_dict = {}
            for k, v in columns.items():
                if hasattr(v, 'model_dump'):
                    columns_dict[k] = v.model_dump()
                else:
                    columns_dict[k] = v.dict()
            
            return {
                "project_id": project_id,
                "project_name": project["name"],
                "columns": columns_dict
            }
            
        except HTTPException:
            raise
        except Exception as e:
            raise HTTPException(
                status_code=self._get_status_code('HTTP_500_INTERNAL_SERVER_ERROR', 500),
                detail=f"获取需求看板失败: {str(e)}"
            )
    
    async def get_requirement_stats(self, user: dict, project_id: Optional[str] = None) -> RequirementStats:
        """获取需求统计数据"""
        try:
            # 构建查询条件（使用公共方法）
            query = self._build_query_filters(
                user=user,
                project_id=project_id
            )
            
            # 获取所有需求
            requirements = await self.db.requirements.find(query).to_list(length=None)
            
            # 获取特定状态代码
            approved_status_code = await self.get_state_code_by_name_keywords("requirement", ["已批准", "批准", "approved"])
            implemented_status_code = await self.get_state_code_by_name_keywords("requirement", ["已实现", "实现", "implemented"])
            
            # 统计数据
            total_requirements = len(requirements)
            approved_requirements = len([r for r in requirements if approved_status_code and r["status"] == approved_status_code])
            implemented_requirements = len([r for r in requirements if implemented_status_code and r["status"] == implemented_status_code])
            
            # 计算逾期需求（使用同步方法优化）
            overdue_requirements = 0
            for req in requirements:
                status_code = req.get("status", "")
                # 从状态管理系统获取完成状态列表
                from app.utils.state_utils import get_completed_state_codes
                completed_statuses = await get_completed_state_codes(self.db, "requirement")
                if self._is_overdue(req.get("due_date"), status_code, completed_statuses):
                    overdue_requirements += 1
            
            # 按状态分组统计（使用动态状态）
            requirement_states = await self.get_module_states("requirement")
            requirements_by_status = {}
            for state in requirement_states:
                requirements_by_status[state.code] = len([r for r in requirements if r["status"] == state.code])
            
            # 按优先级分组统计
            requirements_by_priority = {}
            for priority in RequirementPriority:
                requirements_by_priority[priority.value] = len([r for r in requirements if r["priority"] == priority])
            
            # 按类型分组统计（从统一配置管理获取类型列表）
            requirements_by_type = {}
            type_docs = await self.db.types.find({"module_type": "requirement", "is_enabled": True}).to_list(length=None)
            for type_doc in type_docs:
                type_code = type_doc["code"]
                requirements_by_type[type_code] = len([r for r in requirements if r.get("requirement_type") == type_code])
            
            # 按来源分组统计（从统一配置管理获取来源列表）
            requirements_by_source = {}
            source_docs = await self.db.sources.find({"module_type": "requirement", "is_enabled": True}).to_list(length=None)
            for source_doc in source_docs:
                source_code = source_doc["code"]
                requirements_by_source[source_code] = len([r for r in requirements if r.get("source") == source_code])
            
            return RequirementStats(
                total_requirements=total_requirements,
                approved_requirements=approved_requirements,
                implemented_requirements=implemented_requirements,
                overdue_requirements=overdue_requirements,
                requirements_by_status=requirements_by_status,
                requirements_by_priority=requirements_by_priority,
                requirements_by_type=requirements_by_type,
                requirements_by_source=requirements_by_source
            )
            
        except HTTPException:
            raise
        except Exception as e:
            raise HTTPException(
                status_code=self._get_status_code('HTTP_500_INTERNAL_SERVER_ERROR', 500),
                detail=f"获取需求统计失败: {str(e)}"
            )
    
    async def bulk_update_requirements(self, user: dict, requirement_ids: List[str], update_data: dict) -> dict:
        """批量更新需求"""
        try:
            # 验证需求ID格式
            requirement_ids_obj = [self._validate_object_id(req_id, f"需求ID ({req_id})") for req_id in requirement_ids]
            
            # 查询需求并检查权限
            requirements = await self.db.requirements.find({"_id": {"$in": requirement_ids_obj}}).to_list(length=None)
            if len(requirements) != len(requirement_ids_obj):
                raise HTTPException(
                    status_code=self._get_status_code('HTTP_404_NOT_FOUND', 404),
                    detail="部分需求不存在"
                )
            
            # 权限检查
            user_projects = user.get("projects", [])
            for req in requirements:
                if user_projects and str(req["project_id"]) not in user_projects:
                    raise HTTPException(
                        status_code=self._get_status_code('HTTP_403_FORBIDDEN', 403),
                        detail="无权限修改部分需求"
                    )
            
            # 构建更新数据
            final_update_data = update_data.copy()
            final_update_data["updated_at"] = datetime.utcnow()
            
            # 状态变更时更新相应的时间戳
            if "status" in final_update_data:
                # 获取特定状态代码
                approved_status_code = await self.get_state_code_by_name_keywords("requirement", ["已批准", "批准", "approved"])
                implemented_status_code = await self.get_state_code_by_name_keywords("requirement", ["已实现", "实现", "implemented"])
                if approved_status_code and final_update_data["status"] == approved_status_code:
                    final_update_data["approved_at"] = datetime.utcnow()
                elif implemented_status_code and final_update_data["status"] == implemented_status_code:
                    final_update_data["implemented_at"] = datetime.utcnow()
            
            # 处理 assignee_id 转换
            if "assignee_id" in final_update_data:
                assignee_id_value = final_update_data["assignee_id"]
                if assignee_id_value:
                    # 验证负责人是否存在
                    assignee_id_obj = self._to_object_id(assignee_id_value)
                    if assignee_id_obj:
                        assignee = await self.db.users.find_one({"_id": assignee_id_obj})
                        if not assignee:
                            raise HTTPException(
                                status_code=self._get_status_code('HTTP_404_NOT_FOUND', 404),
                                detail="指定的负责人不存在"
                            )
                        final_update_data["assignee_id"] = assignee_id_obj
                    else:
                        raise HTTPException(
                            status_code=self._get_status_code('HTTP_400_BAD_REQUEST', 400),
                            detail="无效的负责人ID"
                        )
                else:
                    final_update_data["assignee_id"] = None

            assignee_update_present = "assignee_id" in final_update_data
            status_update_value = final_update_data.get("status")

            for req in requirements:
                status_value = status_update_value or req.get("status")
                status_lower = (status_value or "").lower()
                if not status_lower:
                    # 从状态管理系统获取默认状态
                    from app.core.state_validator import get_state_validator
                    state_validator = get_state_validator(self.db)
                    default_status = await state_validator.get_default_state_code("requirement")
                    if default_status:
                        status_lower = default_status.lower()
                    else:
                        logging.warning("无法从状态管理系统获取需求的默认状态")
                        status_lower = ""
                assignee_value = final_update_data.get("assignee_id") if assignee_update_present else req.get("assignee_id")
                if assignee_value in ("", None):
                    assignee_value = None
                if not assignee_value and not await self._status_allows_unassigned(status_lower):
                    raise HTTPException(
                        status_code=self._get_status_code('HTTP_400_BAD_REQUEST', 400),
                        detail=f"需求 {req.get('_id')} 的状态“{status_value or '当前状态'}”下必须指定负责人"
                    )
            
            # 批量更新需求
            result = await self.db.requirements.update_many(
                {"_id": {"$in": requirement_ids_obj}},
                {"$set": final_update_data}
            )
            
            return {
                "message": f"成功更新 {result.modified_count} 个需求",
                "updated_count": result.modified_count
            }
            
        except HTTPException:
            raise
        except Exception as e:
            raise HTTPException(
                status_code=self._get_status_code('HTTP_500_INTERNAL_SERVER_ERROR', 500),
                detail=f"批量更新需求失败: {str(e)}"
            )
    
    async def add_requirement_comment(self, user: dict, requirement_id: str, comment_in: RequirementCommentCreate) -> dict:
        """添加需求评论"""
        try:
            # 验证需求ID格式
            requirement_id_obj = self._validate_object_id(requirement_id, "需求ID")
            
            # 查询需求
            requirement = await self.db.requirements.find_one({"_id": requirement_id_obj})
            if not requirement:
                raise HTTPException(
                    status_code=self._get_status_code('HTTP_404_NOT_FOUND', 404),
                    detail="需求不存在"
                )
            
            # 权限检查
            user_projects = user.get("projects", [])
            if user_projects and str(requirement["project_id"]) not in user_projects:
                raise HTTPException(
                    status_code=self._get_status_code('HTTP_403_FORBIDDEN', 403),
                    detail="无权限为此需求添加评论"
                )
            
            # 如果是回复评论，验证父评论是否存在
            if comment_in.parent_id:
                parent_comment_exists = await self.db.requirements.find_one({
                    "_id": requirement_id_obj,
                    "comments.id": comment_in.parent_id,
                    "comments.is_deleted": {"$ne": True}
                })
                if not parent_comment_exists:
                    raise HTTPException(
                        status_code=self._get_status_code('HTTP_404_NOT_FOUND', 404),
                        detail="父评论不存在或已被删除"
                    )
            
            # 构建评论数据
            comment = {
                "id": str(ObjectId()),
                "user_id": user["user_id"],
                "user_name": user["username"],
                "content": comment_in.content,
                "parent_id": comment_in.parent_id,
                "is_deleted": False,
                "created_at": datetime.utcnow(),
                "updated_at": None,
                "deleted_at": None
            }
            
            # 添加评论
            await self.db.requirements.update_one(
                {"_id": requirement_id_obj},
                {
                    "$push": {"comments": comment},
                    "$set": {"updated_at": datetime.utcnow()}
                }
            )
            
            return {"message": "评论添加成功", "comment": comment}
            
        except HTTPException:
            raise
        except Exception as e:
            raise HTTPException(
                status_code=self._get_status_code('HTTP_500_INTERNAL_SERVER_ERROR', 500),
                detail=f"添加需求评论失败: {str(e)}"
            )
    
    async def update_requirement_comment(self, user: dict, requirement_id: str, comment_id: str, comment_in: RequirementCommentUpdate) -> dict:
        """更新需求评论"""
        try:
            # 验证需求ID格式
            requirement_id_obj = self._validate_object_id(requirement_id, "需求ID")
            
            # 查询需求和评论
            requirement = await self.db.requirements.find_one({
                "_id": requirement_id_obj,
                "comments.id": comment_id
            })
            if not requirement:
                raise HTTPException(
                    status_code=self._get_status_code('HTTP_404_NOT_FOUND', 404),
                    detail="需求或评论不存在"
                )
            
            # 找到要更新的评论
            comment_to_update = None
            for comment in requirement.get("comments", []):
                if comment["id"] == comment_id:
                    comment_to_update = comment
                    break
            
            if not comment_to_update:
                raise HTTPException(
                    status_code=self._get_status_code('HTTP_404_NOT_FOUND', 404),
                    detail="评论不存在"
                )
            
            # 检查评论是否已被删除
            if comment_to_update.get("is_deleted", False):
                raise HTTPException(
                    status_code=self._get_status_code('HTTP_400_BAD_REQUEST', 400),
                    detail="无法编辑已删除的评论"
                )
            
            # 权限检查：只有评论作者可以编辑
            if comment_to_update["user_id"] != user["user_id"]:
                raise HTTPException(
                    status_code=self._get_status_code('HTTP_403_FORBIDDEN', 403),
                    detail="只能编辑自己的评论"
                )
            
            # 更新评论
            await self.db.requirements.update_one(
                {
                    "_id": requirement_id_obj,
                    "comments.id": comment_id
                },
                {
                    "$set": {
                        "comments.$.content": comment_in.content,
                        "comments.$.updated_at": datetime.utcnow(),
                        "updated_at": datetime.utcnow()
                    }
                }
            )
            
            return {"message": "评论更新成功"}
            
        except HTTPException:
            raise
        except Exception as e:
            raise HTTPException(
                status_code=self._get_status_code('HTTP_500_INTERNAL_SERVER_ERROR', 500),
                detail=f"更新需求评论失败: {str(e)}"
            )
    
    async def delete_requirement_comment(self, user: dict, requirement_id: str, comment_id: str) -> dict:
        """删除需求评论（软删除）"""
        try:
            # 验证需求ID格式
            requirement_id_obj = self._validate_object_id(requirement_id, "需求ID")
            
            # 查询需求和评论
            requirement = await self.db.requirements.find_one({
                "_id": requirement_id_obj,
                "comments.id": comment_id
            })
            if not requirement:
                raise HTTPException(
                    status_code=self._get_status_code('HTTP_404_NOT_FOUND', 404),
                    detail="需求或评论不存在"
                )
            
            # 找到要删除的评论
            comment_to_delete = None
            for comment in requirement.get("comments", []):
                if comment["id"] == comment_id:
                    comment_to_delete = comment
                    break
            
            if not comment_to_delete:
                raise HTTPException(
                    status_code=self._get_status_code('HTTP_404_NOT_FOUND', 404),
                    detail="评论不存在"
                )
            
            # 检查评论是否已被删除
            # 兼容处理：检查status或is_deleted字段
            comment_status = comment_to_delete.get("status", "published")
            is_deleted = comment_to_delete.get("is_deleted", False)
            if comment_status == "deleted" or is_deleted:
                raise HTTPException(
                    status_code=self._get_status_code('HTTP_400_BAD_REQUEST', 400),
                    detail="评论已被删除"
                )
            
            # 权限检查：只有评论作者可以删除
            if comment_to_delete["user_id"] != user["user_id"]:
                raise HTTPException(
                    status_code=self._get_status_code('HTTP_403_FORBIDDEN', 403),
                    detail="只能删除自己的评论"
                )
            
            # 软删除评论
            await self.db.requirements.update_one(
                {
                    "_id": requirement_id_obj,
                    "comments.id": comment_id
                },
                {
                    "$set": {
                        "comments.$.status": "deleted",  # 使用动态状态
                        "comments.$.is_deleted": True,  # 保持向后兼容
                        "comments.$.deleted_at": datetime.utcnow(),
                        "updated_at": datetime.utcnow()
                    }
                }
            )
            
            return {"message": "评论删除成功"}
            
        except HTTPException:
            raise
        except Exception as e:
            raise HTTPException(
                status_code=self._get_status_code('HTTP_500_INTERNAL_SERVER_ERROR', 500),
                detail=f"删除需求评论失败: {str(e)}"
            )
    
    def _build_requirement_tree(self, requirements: List[RequirementResponse]) -> List[RequirementResponse]:
        """构建需求树形结构"""
        # 创建需求字典，key 为需求ID
        requirement_dict = {req.id: req for req in requirements}
        
        # 创建根节点列表
        root_requirements = []
        
        # 遍历所有需求，构建父子关系
        for req in requirements:
            if req.parent_id and req.parent_id in requirement_dict:
                # 有父需求，添加到父需求的 children 中
                parent = requirement_dict[req.parent_id]
                parent.children.append(req)
                parent.children_count = len(parent.children)
            else:
                # 没有父需求或父需求不在当前列表中，作为根节点
                root_requirements.append(req)
        
        # 按 sort_order 和 created_at 排序
        def sort_key(req: RequirementResponse):
            return (req.sort_order, req.created_at)
        
        def sort_tree(reqs: List[RequirementResponse]):
            """递归排序树"""
            sorted_reqs = sorted(reqs, key=sort_key)
            for req in sorted_reqs:
                if req.children:
                    sort_tree(req.children)
            return sorted_reqs
        
        return sort_tree(root_requirements)